package com.doublefish.doublefishrobot.utils.db.database;

import net.sqlcipher.Cursor;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 作者:dugaofeng on 2024/10/16 14:38
 */
public class SQLiteSentencMakeUtil {

    private static final String ORDERS = "orders"; //排序字段的 key
    private static final String ORDERKEY = "orderkey"; //排序字段的 顺序 升序or降序
    private static final String DESC = "DESC";
    private static final String ASC = "ASC";
    private static final String PAGESIZE = "pagesize";
    private static final String PAGENUM = "pagenum";
    private static final int PAGESIZE_NUM = 20;
    private static final int PAGENUM_NUM = 1;


    //获取 排序语句
    public static String makeOrderSentence(Map<String,Object> data) {
        return makeOrderSentence(data,null,null);
    }

    //获取 排序语句  联表查询 要加入表的别名 来识别排序的字段 （注意：必须是别名）
    public static String makeOrderSentence(Map<String,Object> data,String tableName,String tableAliasName) {
        return makeOrderSentence(data,tableName,tableAliasName,null,null);

    }

    /**
     * 排序语句
     * @param data  排序的关键字
     * @param tableName 表名
     * @param tableAliasName  表别名
     * @param defaultOrderStr 默认排序字段
     * @param defaultOrderKey 默认的排序升降序
     * @return
     */
    public static String makeOrderSentence(Map<String,Object> data,String tableName,String tableAliasName ,String defaultOrderStr,String defaultOrderKey) {
        if (data == null || data.size() == 0) return "";

        String prefix;
        if (tableAliasName == null || "".equals(tableAliasName.trim())) {
            prefix = "";
        } else {
            prefix = tableAliasName + ".";
        }

        StringBuilder sb = new StringBuilder();

        List orders = null; //排序字段
        if (data.containsKey(ORDERS)) {
            Object object = data.get(ORDERS);
            if (object instanceof List) {
                orders = (List) object;
            }
        }

        if (orders == null || orders.size() == 0) {
            //没有指定的排序
            if(defaultOrderStr == null || "".equals(defaultOrderStr.trim())) {
                return "";
            } else {
                sb.append(" order by ").append(ignoreTableAliasNameForOrder(tableName,defaultOrderStr) ? "":prefix).append(defaultOrderStr).append(" ").append(defaultOrderKey == null ? DESC:defaultOrderKey).append(" ");
                return sb.toString();
            }
        } else {
            StringBuilder sborder = new StringBuilder();
            for (Object obj : orders) {
                sborder.append(ignoreTableAliasNameForOrder(tableName,obj.toString()) ? "" : prefix).append(obj.toString()).append(",");
            }
            String orderstr = sborder.toString();
            if (orderstr.length() > 0)
                orderstr = orderstr.substring(0, orderstr.length() - 1);  //-1是为了去掉 最后一个 ','


            String orderKey = DESC;
            if (data.containsKey(ORDERKEY)) {
                orderKey = data.get(ORDERKEY).toString();
            }
            sb.append(" order by ").append(orderstr).append(" ").append(orderKey).append(" ");

            return sb.toString();
        }
    }


    /**
     * 获取 分页语句
     * @param data 分页的关键数据
     * @return
     */
    public static String makePageSentence(Map<String,Object> data) {
        if (data == null) return "";

        int pageSize = PAGESIZE_NUM; // 每页条数
        int pageNum = PAGENUM_NUM;   //当前第几页

        if (data.containsKey(PAGESIZE)) {
            pageSize = (int)data.get(PAGESIZE);
        }
        if (data.containsKey(PAGENUM)) {
            pageNum = (int)data.get(PAGENUM);
        }

        if (pageSize <= 0 || pageNum <= 0) {
            return "";
        }
        return String.format(" limit %d offset %d ", pageSize, pageSize * (pageNum - 1));
    }


    //    /************* 有些排序字段是 计算型的字段 不需要表别名 ********************/
//    //需要过滤的字段
//    private static final String[] table_goods_stock_orders = {"real_base_qty","costs","abs_base_qty","shop_price"};  //table_goods_stock 要过滤的字段
//    private static final String[] table_buddy_info_orders = {"payable_money","businesstime"};  //table_buddy_info 要过滤的字段
//
    //需要过滤字段的hashMap
    private static final Map<String,String[]> ordersMap;
    static {
        ordersMap = new HashMap<>();
//        ordersMap.put("table_goods_stock",table_goods_stock_orders);
//        ordersMap.put("table_buddy_info",table_buddy_info_orders);
    }

    //某些字段是计算型的字段 不能再前面加 表的别名 必须过滤掉
    //exampl real_base_qty 就算是 table_goods_stock 中的计算型字段的排序
    private static boolean ignoreTableAliasNameForOrder(String tableName,String order){
        if (tableName == null)
            return false;

        String[] orders = ordersMap.get(tableName);
        if (orders == null || orders.length == 0)
            return false;

        for (int i = 0; i < orders.length; i++) {
            if (orders[i].equals(order)) {
                return true;  //相同表示 要过滤掉 表的别名
            }
        }

        return false;  //不需要过滤掉 表的别名
    }


    /** sql语句where的合成方法 **/
    public static StringBuilder appendQueryWhere(StringBuilder whereStringBuilder, String where) {
        if (whereStringBuilder == null) {
            whereStringBuilder = new StringBuilder();
        }
        if (whereStringBuilder.length() == 0) {
            whereStringBuilder.append(" where ").append(where);
        } else {
            whereStringBuilder.append(" and ").append(where);
        }
        return whereStringBuilder;
    }

    /**
     * 生成where条件语句
     * @param fieldOfTable  表对应的字段关系
     * @param data   where条件
     * @return
     */
    public static String makeQueryWhere(Map<String,Integer> fieldOfTable, Map<String,Object> data) {
        if (data == null || data.size() == 0)
            return " ";

        StringBuilder whereStringBuilder = new StringBuilder();

        for (String key:data.keySet()) {
            String where = key + "=";
            int fieldType = getFieldType(fieldOfTable,key);
            switch (fieldType) {
                case Cursor.FIELD_TYPE_INTEGER:
                case Cursor.FIELD_TYPE_FLOAT:
                    where = where + data.get(key);
                    break;
                case Cursor.FIELD_TYPE_NULL:
                    //不包含该字段
                    break;
                default:
                    where = where +"'" + data.get(key) + "'";
                    break;
            }
            appendQueryWhere(whereStringBuilder, where);
        }
        return whereStringBuilder.toString();
    }

    /*
     * @param key       要匹配的数据库字段 （如  name 或者  tabel.name）
     * @param keywords  搜索的关键字
     * */
    public static String makeLikeSentence(String key,String keywords) {
        if (key == null || key.isEmpty() || keywords == null || keywords.isEmpty()) {
            return "";
        }
        return "("+ key + " >='" + keywords + "' and " + key + " < '" + makeStringAfterLastChar(keywords) + "')";
    }

    public static String makeLikeSentence(String[] keys,String keywords) {
        if (keys == null || keys.length == 0 || keywords == null || keywords.isEmpty()) {
            return "";
        }
        if (keys.length == 1) {
            return makeLikeSentence(keys[0],keywords);
        }

        StringBuilder sb = new StringBuilder();
        sb.append(" ( ");
        for (int i = 0; i < keys.length; i++) {
            sb.append(makeLikeSentence(keys[i],keywords));
            if (i < keys.length - 1) {
                sb.append(" or "); //最后一个不要
            }
        }
        sb.append(" ) ");

        return sb.toString();
    }

    /*** 右like 语句的优化 方法 ****/
    // 将 like 'abc%' 改为 >= 'abc'and < 'abd' 可以用到索引的优化
    //生成比当前字符串大1的字符串
    private static String makeStringAfterLastChar(String str) {
        if (str == null || str.length() == 0) {
            return str;
        }

        //上面的逻辑可以简化
        byte[] bytes = str.getBytes();
        int length = bytes.length;
        bytes[length - 1] += 1;
        return new String(bytes);
    }


    /**
     * 返回字段对应的类型
     * @param fieldOfTable 表的字段信息
     * @param fieldName   字段名
     * @return   字段对应的类型
     */
    private static int getFieldType(Map<String,Integer> fieldOfTable,String fieldName) {
        if (fieldOfTable.containsKey(fieldName)) {
            return fieldOfTable.get(fieldName);
        }
        return Cursor.FIELD_TYPE_NULL; //不包含该字段
    }
}

